--
-- Database: `kuglaskiklub`
--

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Tables definition
--

CREATE TABLE IF NOT EXISTS `igraci` (
  `Sportska legitimacija` varchar(10) NOT NULL,
  `Prezime` varchar(30) NOT NULL,
  `Ime` varchar(25) NOT NULL,
  `Godina rodjenja` smallint(6) NOT NULL,
  `Pol` char(1) NOT NULL,
  `Telefon` varchar(20) DEFAULT NULL,
  `Email` varchar(40) DEFAULT NULL,
  `Kategorija` tinyint(3) unsigned NOT NULL,
  `Klub` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `kategorije` (
  `ID kategorije` tinyint(3) unsigned NOT NULL,
  `Naziv` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `klub` (
  `PIB` int(10) unsigned NOT NULL,
  `Naziv` varchar(30) NOT NULL,
  `Telefon` varchar(25) DEFAULT NULL,
  `EMail` varchar(40) DEFAULT NULL,
  `Ulica i broj` varchar(50) DEFAULT NULL,
  `Godina osnivanja` smallint(6) DEFAULT NULL,
  `Mesto` int(11) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `mesta` (
  `Naziv` varchar(30) NOT NULL,
  `ID mesta` int(11) unsigned NOT NULL,
  `PTT` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `nastupi` (
  `RB utakmice` int(11) NOT NULL,
  `Legitimacija` varchar(10) NOT NULL,
  `Pune` int(10) unsigned NOT NULL,
  `Ciscenje` int(10) unsigned NOT NULL,
  `Ukupno` int(10) unsigned NOT NULL,
  `Poeni protivnik` decimal(2,1) unsigned NOT NULL,
  `Set utakmica` decimal(2,1) unsigned NOT NULL,
  `Prazne` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `utakmica` (
  `RB utakmice` int(11) NOT NULL,
  `Datum` date NOT NULL,
  `Kuglana` varchar(40) NOT NULL,
  `Mesto` int(11) NOT NULL,
  `Domacin` int(10) unsigned NOT NULL,
  `Gost` int(10) unsigned NOT NULL,
  `Cunjeva ukupno domacin` smallint(6) NOT NULL,
  `Cunjeva ukupno gost` smallint(6) NOT NULL,
  `Bodovi domacin` tinyint(4) NOT NULL,
  `Bodovi  gost` tinyint(4) NOT NULL,
  `Set domacin` decimal(2,1) NOT NULL,
  `Set gost` decimal(2,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes, Keys
--

ALTER TABLE `igraci`
 ADD PRIMARY KEY (`Sportska legitimacija`), ADD KEY `Prezime` (`Prezime`,`Ime`,`Kategorija`), ADD KEY `Kategorija` (`Kategorija`), ADD KEY `Klub` (`Klub`);

ALTER TABLE `kategorije`
 ADD PRIMARY KEY (`ID kategorije`);

ALTER TABLE `klub`
 ADD PRIMARY KEY (`PIB`), ADD KEY `Naziv` (`Naziv`), ADD KEY `fkmestokluba` (`Mesto`);

ALTER TABLE `mesta`
 ADD PRIMARY KEY (`ID mesta`);

ALTER TABLE `nastupi`
 ADD PRIMARY KEY (`RB utakmice`,`Legitimacija`), ADD KEY `fklegitimacija` (`Legitimacija`);

ALTER TABLE `utakmica`
 ADD PRIMARY KEY (`RB utakmice`), ADD KEY `Mesto` (`Mesto`,`Domacin`,`Gost`), ADD KEY `Domacin` (`Domacin`), ADD KEY `Gost` (`Gost`);

--
-- Constraints, References
--

ALTER TABLE `igraci`
ADD CONSTRAINT `fkigraciuklubu` FOREIGN KEY (`Klub`) REFERENCES `klub` (`PIB`) ON UPDATE CASCADE,
ADD CONSTRAINT `igraci_ibfk_1` FOREIGN KEY (`Kategorija`) REFERENCES `kategorije` (`ID kategorije`);

ALTER TABLE `klub`
ADD CONSTRAINT `fkmestokluba` FOREIGN KEY (`Mesto`) REFERENCES `mesta` (`ID mesta`) ON UPDATE CASCADE;

ALTER TABLE `nastupi`
ADD CONSTRAINT `fklegitimacija` FOREIGN KEY (`Legitimacija`) REFERENCES `igraci` (`Sportska legitimacija`) ON UPDATE CASCADE,
ADD CONSTRAINT `nastupi_ibfk_1` FOREIGN KEY (`RB utakmice`) REFERENCES `utakmica` (`RB utakmice`) ON DELETE CASCADE;

ALTER TABLE `utakmica`
ADD CONSTRAINT `utakmica_ibfk_1` FOREIGN KEY (`Domacin`) REFERENCES `klub` (`PIB`) ON UPDATE CASCADE,
ADD CONSTRAINT `utakmica_ibfk_2` FOREIGN KEY (`Gost`) REFERENCES `klub` (`PIB`) ON UPDATE CASCADE;

ALTER TABLE `utakmica` CHANGE `Mesto` `Mesto` INT(11) UNSIGNED NOT NULL;

ALTER TABLE `utakmica`
ADD CONSTRAINT `utakmica_fkmesto` FOREIGN KEY (`Mesto`) REFERENCES `mesta` (`Id mesta`) ON UPDATE CASCADE;